# coding:utf-8
__author__ = "Xuhao"


import  datetime
from sqlalchemy import Column, Integer, String, DateTime, Numeric, create_engine, VARCHAR
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from config import DB_CONFIG


#mySql 5.6.38
from data.ISqlHelper import ISqlHelper

BaseModel = declarative_base()

class Novel(BaseModel):
    __tablename__ = 'Novel'
    id = Column(Integer, primary_key=True, autoincrement=True)
    #书名
    bookName = Column(VARCHAR(32), nullable=False)
    #作者
    bookAuthor = Column(VARCHAR(32), nullable=False)
    #URl
    bookUrl = Column(VARCHAR(64), nullable=False)
    #数据库名称
    bookDataName = Column(VARCHAR(32), nullable=True)
    #最新章节名称
    bookNewChapter = Column(VARCHAR(128), nullable=True)
    # 最新章节名URL
    bookNewChapterUrl = Column(VARCHAR(256), nullable=True)
    #更新时间
    bookUpdate = Column(VARCHAR(16), nullable=True)
    #分类
    bookCategory = Column(Integer, default=1)
    #小说状态(完本，连载)
    bookType = Column(VARCHAR(16), nullable=True)
    #小说简介
    bookAbstract = Column(String, nullable=True)
    #小说热度

class SqlHelper(ISqlHelper):
    params = {'bookName': Novel.bookName, 'bookAuthor': Novel.bookAuthor, 'bookUrl': Novel.bookUrl,
              'bookDataName': Novel.bookDataName, 'bookNewChapter': Novel.bookNewChapter, 'bookUpdate': Novel.bookUpdate,
              'bookCategory': Novel.bookCategory, 'bookType': Novel.bookType,
              'bookNewChapterUrl': Novel.bookNewChapterUrl
    }

    def __init__(self):
        self.engine = create_engine(DB_CONFIG['DB_CONNECT_STRING'],echo=False)
        DB_Session = sessionmaker(bind=self.engine)
        self.session = DB_Session()

    def init_db(self):
        BaseModel.metadata.create_all(self.engine)
    def drop_db(self):
        BaseModel.metadata.drop_all(self.engine)

    def insert(self, value=None):
        novel = Novel(bookName=value['bookName'], bookAuthor=value['bookAuthor'], bookUrl=value['bookUrl'],
                      bookDataName=value['bookDataName'], bookNewChapter=value['bookNewChapter'],
                      bookUpdate=value['bookUpdate'], bookCategory=value['bookCategory'],bookType=value['bookType'],
                      bookNewChapterUrl=value['bookNewChapterUrl'])
        self.session.add(novel)
        self.session.commit()

    def update(self, conditions=None, value=None):
        if conditions and value:
            conditon_list = []
            for key in list(conditions.keys()):
                if self.params.get(key, None):
                    conditon_list.append(self.params.get(key) == conditions.get(key))
            conditions = conditon_list
            query = self.session.query(Novel)
            for condition in conditions:
                query = query.filter(condition)
            updatevalue = {}
            for key in list(value.keys()):
                if self.params.get(key, None):
                    updatevalue[self.params.get(key, None)] = value.get(key)
            updateNum = query.update(updatevalue)
            self.session.commit()
        else:
            updateNum = 0
        return {'updateNum': updateNum}

    def select(self, count=None, conditions=None):
        '''
        查找
        :param count:
        :param conditions:
        :return:
        '''

        if conditions:
            conditon_list = []
            for key in list(conditions.keys()):
                if self.params.get(key, None):
                    conditon_list.append(self.params.get(key) == conditions.get(key))
            conditions = conditon_list
        else:
            conditions = []
        query = self.session.query(Novel.bookName, Novel.bookAuthor, Novel.bookUrl, Novel.bookNewChapterUrl, Novel.bookNewChapter)
        if len(conditions) > 0 and count:
            for condition in conditions:
                query = query.filter(condition)
            #return query.order_by(Novel.bookName, Novel.bookAuthor).limit(count).all()
            return query.limit(count).all()
        elif count:
            #return query.order_by(Novel.bookName, Novel.bookAuthor).limit(count).all()
            return query.limit(count).all()
        elif len(conditions) > 0:
            for condition in conditions:
                query = query.filter(condition)
            #return query.order_by(Novel.bookName, Novel.bookAuthor).all()
            return query.all()
        else:
            #return query.order_by(Novel.bookName, Novel.bookAuthor).all()
            return query.all()

        # if conditions:
        #     conditon_list = []
        #     for key in list(conditions.keys()):
        #         if self.params.get(key, None):
        #             conditon_list.append(self.params.get(key) == conditions.get(key))
        #     conditions = conditon_list
        # else:
        #     conditions = []
        #
        # query = self.session.query(Novel)
        # for condition in conditions:
        #     print('condition = %s' % condition)
        #     query = query.filter(condition)
        # return query.all()